JSONL ファイルをもとに Amazon Athena で CREATE TABLE してみる

JSONL ファイルをもとに Amazon Athena で CREATE TABLE してみる

Clock Icon2024.03.29

こんにちは!よしななです。 今回は、JSONL データを Amazon Athena でCREATE TABLEする機会があったので備忘録としてブログに残します。

目次

  • やりたいこと
  • 対象読者
  • 事前準備
    • AWS へのサインイン
    • データを Amazon S3 に配置する
    • JSONL ファイルのアップロード
  • 手順
    • Amazon Athena で JSONL を操作してみる
      • CREATE TABLE の実行
      • テーブルの検索

やりたいこと

  • Amazon S3 にアップロードされた 以下の JSONL ファイルをもとに、Amazon Athena 上でCREATE TABLEを実行します。
  • 作成したテーブルに対し、SELECT文を使用してGradesカラムをJapanese(国語)、Math(数学)、English(英語)…と分けて出力するところまでを実施します。

  • 今回対象となるデータは以下となります。

{"No": 1, "Name": "山田太郎", "Gender": "Male", "Grades": {"Japanese": 82, "Math": 74, "English": 88, "Science": 90, "SocialStudies": 85}}
{"No": 2, "Name": "佐藤花子", "Gender": "Female", "Grades": {"Japanese": 90, "Math": 68, "English": 95, "Science": 87, "SocialStudies": 92}}
{"No": 3, "Name": "鈴木一郎", "Gender": "Male", "Grades": {"Japanese": 75, "Math": 80, "English": 78, "Science": 85, "SocialStudies": 80}}
{"No": 4, "Name": "高橋愛", "Gender": "Female", "Grades": {"Japanese": 88, "Math": 92, "English": 94, "Science": 90, "SocialStudies": 91}}
  • 今回対象となるデータの型は以下の通りです。
カラム名 データ型
No int
Name string
Gender string
Grades struct

対象読者

  • JSONL ファイルをもとに Amazon Athena 上でCREATE TABLEしたい方

事前準備

AWS マネジメントコンソールへのサインイン

AWS マネジメントコンソールからサインインが必要です。
以下の公式ドキュメントを参考に、サインインを行います。
https://docs.aws.amazon.com/ja_jp/signin/latest/userguide/how-to-sign-in.html

データを Amazon S3 に配置する

まず、JSONL ファイルをアップロードするための S3 バケットを作成します。 ホーム画面上部 → 検索コンソールから S3 を検索 → バケットを作成 をクリックします。
バケット作成画面に遷移するので、ここから S3 バケットを作成します。

設定項目

AWS リージョン:アジアパシフィック (東京) ap-northeast-1 を選択します。
バケット名:任意の名前を入力します。今回はjsonl-testに設定します。
その他の設定はデフォルトの状態にします。

JSONL ファイルのアップロード

作成した S3 バケットに、テーブルのデータソースとなる JSONL ファイルのアップロードを行います。作成したバケットを選択 → アップロードをクリックします。

以下の画面に遷移します。
ファイルのアップロードをクリックするとエクスプローラーが開くので、該当のファイルを選択してアップロードします。こちらでファイルのアップロードが完了となります。

CREATE DATABASE の実行

次に、Amazon Athena 上でCREATE DATABASEを実行します。
ホーム画面上部 → 検索コンソールから Amazon Athena を検索 → クエリエディタ をクリックします。
クエリエディタを開いたら、以下を実行し DATABASE を作成します。

CREATE DATABASE jsonl_test;

手順

CREATE TABLE の実行

データベースが作成されたので、CREATE TABLEを実行してみます。

CREATE EXTERNAL TABLE IF NOT EXISTS `jsonl_test_table`(
  `No` int,
  `Name` string,
  `Gender` string,
  `Grades` struct <`Japanese`:int,`Math`:int,`English`:int,`Science`:int,`SocialStudies`:int>
)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
LOCATION
  's3://jsonl-test/';

CREATE TABLE クエリの構文について

上記のクエリの構文について、以下で解説します。
下記以外にもCREATE TABLEのオプションを指定できますが、JSONL ファイルの読み込みには下記の3項目が設定されていればクエリの実行が可能です。
クエリの作成には、以下の公式ドキュメントページを参考にしています。
https://docs.aws.amazon.com/athena/latest/ug/create-table.html

1.CREATE TABLE

  • EXTERNAL
    • Amazon Athena 上でCREATE TABLEする場合、Iceberg テーブルを作成する場合以外でEXTERNALが抜けているとエラーになります。
  • IF NOT EXISTS
    • 同じ名前のテーブルが既にデータベースに存在する場合、CREATE TABLEをスキップします。

2. ROW FORMAT SERDE

  • 作成するテーブルのデータ形式を指定します。
  • テーブル作成元のデータ形式とROW FORMAT SERDEの形式が一致していないとエラーになります。
  • 今回は、JSONL ファイルを読み込む必要があるので'org.openx.data.jsonserde.JsonSerDe'を指定します。

3. LOCATION

  • データが配置されているバケット名を入力します。
  • 今回は、jsonl-test直下にtest_jsonl.jsonlを配置したのでs3://jsonl-test/を入力します。

オブジェクト型について

オブジェクト型のデータを CREATE TABLEするには、struct<col_name:data_type,...>と記述します。

出力結果

テーブルが作成されたので、SELECT * FROM "jsonl_test"."jsonl_test_table" limit 10;を実行しテーブルの中身を確認してみます。


問題なくテーブルが出力されました!

テーブルの検索

次に、SELECT文を使用してGradesカラムをJapanese(国語)、Math(数学)、English(英語)…と分けて出力してみたいと思います。

SELECT
    "No",
    "Name",
    "Gender",
    "Grades"."Japanese" as "国語",
    "Grades"."Math" as "数学",
    "Grades"."English" as "英語"
FROM "jsonl_test"."jsonl_test_table" limit 10;

SELECT FROM クエリの構文について

1. Grades カラムの展開

Gradesカラムを Japanese(国語)、Math(数学)、English(英語)… と分割して出力するには、{カラム名}.{オブジェクトの項目名}で出力が可能です。

出力結果

上記のクエリを実行すると、以下の通りGradesカラムをJapanese(国語)、Math(数学)、English(英語)と分けて出力することができました!

以上で、JSONL ファイルをもとに Amazon Athena でCREATE TABLEしてみる は完了となります。ここまで読んでいただきありがとうございました!

この記事をシェアする

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.